/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sistema.aplicacao;

import com.interact.sas.cmn.ApplicationContext;
import com.interact.sas.cmn.db.services.Database;
import com.interact.sas.cmn.db.services.QueryResult;
import com.interact.sas.cmn.util.Login;
import com.interact.sas.cmn.util.Prompts;
import com.interact.sas.qms.data.Occurrence;
import com.interact.sas.qms.data.OccurrenceType;
import com.interact.sas.qms.util.Inspector;
import com.interact.sas.qms.util.OccurrenceFormFactory;
import java.awt.event.ActionEvent;
import java.awt.event.MouseEvent;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.sql.*;
import com.sistema.db.*;
import com.sistema.tools.Data;
import com.sistema.tools.ValidaString;
import java.awt.event.ActionListener;
import java.awt.event.MouseListener;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import javax.swing.text.MaskFormatter;
import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.util.JRLoader;
import net.sf.jasperreports.view.JasperViewer;
import org.jdesktop.swingx.JXDatePicker;

/**
 *
 * @author geraldo
 */
public class DialogAdminLigacoes extends JDialog
{

    private JTable tblLigacoes;
    private DefaultTableModel modeloTabela;
    private JScrollPane scroller;
    private JXDatePicker dt_inicio;
    private JXDatePicker dt_fim;
    private JFormattedTextField txt_hora_inicio;
    private JFormattedTextField txt_hora_fim;
    private MaskFormatter mascaraHoraInicio;
    private MaskFormatter mascaraHoraFim;
    private JComboBox cbxUsuarios;
    private JComboBox cbxClientes;
    private JTextField tfCodigoTicket;
    private JButton btnLimpar;
    private JButton btnImprimir;
    private JLabel lblTotal_ligacoes;
    private JLabel lblTotal_realizadas;
    private JLabel lblTotal_recebidas;
    private boolean controleColunas = false;
    private JPopupMenu pop;
    private JMenu menu;
    private JMenuItem itemVisualizar;
    private JMenu menu2;
    private JMenuItem itemCadastrarTicket;
    private JPopupMenu pop2;
    private Occurrence ocorrencia = new Occurrence();
    private LerProperties properties = new LerProperties();
    private String consulta = "";

    public DialogAdminLigacoes()
    {

        setTitle( "Controle de Ligações Suporte" );
        setSize( 800, 700 );
        setLayout( null );
        setLocationRelativeTo( null );
        iniciaComponentes();
        actions();
        realizaFiltro();
//      setModal( true );
        setVisible( true );

    }

    public void iniciaComponentes()
    {

        add( new JLabel( "Data Inicio:" ) ).setBounds( 60, 20, 80, 25 );
        dt_inicio = new JXDatePicker();
        add( dt_inicio ).setBounds( 170, 20, 150, 25 );

        add( new JLabel( "Data Fim:" ) ).setBounds( 390, 20, 80, 25 );
        dt_fim = new JXDatePicker();
        add( dt_fim ).setBounds( 480, 20, 150, 25 );

        //Mascara para hora inicio
        mascaraHoraInicio = new MaskFormatter();
        mascaraHoraInicio.setValidCharacters( "0123456789" );
        mascaraHoraInicio.setPlaceholderCharacter( '_' );

        try
        {
            mascaraHoraInicio.setMask( "##:##:##" );

        } catch ( Exception e )
        {

            JOptionPane.showMessageDialog( null, "Problema com a máscara!!" );

        }


        //Mascara para hora inicio
        mascaraHoraFim = new MaskFormatter();
        mascaraHoraFim.setValidCharacters( "0123456789" );
        mascaraHoraFim.setPlaceholderCharacter( '_' );

        try
        {
            mascaraHoraFim.setMask( "##:##:##" );

        } catch ( Exception e )
        {

            JOptionPane.showMessageDialog( null, "Problema com a máscara!!" );

        }


        add( new JLabel( "Hora Inicio:" ) ).setBounds( 60, 50, 80, 25 );
        txt_hora_inicio = new JFormattedTextField( mascaraHoraInicio );
        add( txt_hora_inicio ).setBounds( 170, 50, 150, 25 );

        add( new JLabel( "Hora Fim:" ) ).setBounds( 390, 50, 80, 25 );
        txt_hora_fim = new JFormattedTextField( mascaraHoraFim );
        add( txt_hora_fim ).setBounds( 480, 50, 150, 25 );

        //ComboBox usuários

        Banco conn = new Banco();
        ResultSet rs = conn.executarConsulta( "SELECT nome FROM usuarios" );
        conn.fecha();

        cbxUsuarios = new JComboBox();

        try
        {
            cbxUsuarios.addItem( "Todos" );

            while ( rs.next() )
            {
                cbxUsuarios.addItem( rs.getString( "nome" ) );
            }
        } catch ( Exception e )
        {
        }

        cbxUsuarios.setSelectedIndex( 0 );
        add( new JLabel( "Usuários: " ) ).setBounds( 60, 80, 80, 25 );
        add( cbxUsuarios ).setBounds( 170, 80, 460, 25 );

        //ComboBox Clientes

        Banco conn2 = new Banco();
        ResultSet rs2 = conn2.executarConsulta( "SELECT descricao FROM clientes" );
        conn2.fecha();

        cbxClientes = new JComboBox();

        try
        {
            cbxClientes.addItem( "Todos" );

            while ( rs2.next() )
            {
                cbxClientes.addItem( rs2.getString( "descricao" ) );
            }
        } catch ( Exception e )
        {
        }

        cbxClientes.setSelectedIndex( 0 );
        add( new JLabel( "Clientes: " ) ).setBounds( 60, 110, 80, 25 );
        add( cbxClientes ).setBounds( 170, 110, 460, 25 );


        //Busca Ticket
        add( new JLabel( "Ticket: " ) ).setBounds( 60, 140, 80, 25 );
        tfCodigoTicket = new JTextField();
        add( tfCodigoTicket ).setBounds( 170, 140, 150, 25 );


        //Botoes

        btnImprimir = new JButton( "Imprimir" );
        btnImprimir.setIcon( new ImageIcon( ( getClass().getResource( "/img/img_imp.png" ) ) ) );
        add( btnImprimir ).setBounds( 250, 185, 150, 25 );

        btnLimpar = new JButton( "Limpar Filtro" );
        btnLimpar.setIcon( new ImageIcon( getClass().getResource( "/img/img_limpar.png" ) ) );
        add( btnLimpar ).setBounds( 410, 185, 150, 25 );


        String consulta = ( "SELECT b.id, "
                + "a.descricao as cliente, "
                + "b.ref_cliente, "
                + "b.lig_recebida, "
                + "b.lig_realizada, "
                + "b.ticket, "
                + "b.ref_ticket, "
                + "c.nome as responsavel, "
                + "b.ref_usuario, "
                + "b.data, "
                + "b.hora "
                + "FROM clientes a "
                + "INNER JOIN ligacoes b "
                + "ON a.id=b.ref_cliente "
                + "INNER JOIN usuarios c "
                + "ON b.ref_usuario=c.id ORDER BY b.id" );

        criaModeloTabela( consulta );


        tblLigacoes = new JTable( modeloTabela )
        {

            public boolean isCellEditable( int rowIndex, int vColIndex )
            {
                return false;
            }
        };

        personalizaTabela();

        tblLigacoes.setSelectionMode( ListSelectionModel.SINGLE_SELECTION );

        scroller = new JScrollPane( tblLigacoes );
        add( scroller ).setBounds( 50, 260, 693, 350 );


        add( new JLabel( "Recebidas: " ) ).setBounds( 75, 610, 90, 25 );
        add( lblTotal_recebidas = new JLabel( "" ) ).setBounds( 155, 610, 100, 30 );

        add( new JLabel( "Realizadas: " ) ).setBounds( 335, 610, 100, 30 );
        add( lblTotal_realizadas = new JLabel( "" ) ).setBounds( 422, 610, 100, 30 );

        add( new JLabel( "Total de Ligações: " ) ).setBounds( 550, 610, 140, 30 );
        add( lblTotal_ligacoes = new JLabel( "" ) ).setBounds( 682, 610, 100, 30 );



        //JPopupMenu
        menu = new JMenu();
        itemVisualizar = new JMenuItem( "Visualizar ticket" );
        itemVisualizar.setIcon( new ImageIcon( getClass().getResource( "/img/img_visualizar.png" ) ) );
        menu.add( itemVisualizar );

        pop = menu.getPopupMenu();


        //JPopupMenu2
        menu2 = new JMenu();
        itemCadastrarTicket = new JMenuItem( "Cadastrar Ticket" );
        itemCadastrarTicket.setIcon( new ImageIcon( getClass().getResource( "/img/img_ticket.png" ) ) );
        menu2.add( itemCadastrarTicket );

        pop2 = menu2.getPopupMenu();


    }

    public void criaModeloTabela( String sql )
    {
        if ( controleColunas == false )
        {
            modeloTabela = new DefaultTableModel();
            modeloTabela.addColumn( "codigo" );
            modeloTabela.addColumn( "Cliente" );
            modeloTabela.addColumn( "Ligações Recebidas" );
            modeloTabela.addColumn( "Ligações Realizadas" );
            modeloTabela.addColumn( "Ticket" );
            modeloTabela.addColumn( "ref_ticket" );
            modeloTabela.addColumn( "Responsável" );
            modeloTabela.addColumn( "Data" );
            modeloTabela.addColumn( "Hora" );
            controleColunas = true;
        }

        Banco conn3 = new Banco();
        String data;
        ResultSet rs3 = conn3.executarConsulta( sql );
        conn3.fecha();


        try
        {
            Data dt = new Data();
            while ( rs3.next() )
            {
                data = rs3.getString( "data" );
                data = dt.inverteAplicacao( data );
                modeloTabela.addRow( new Object[]
                        {
                            rs3.getInt( "id" ),
                            rs3.getString( "cliente" ),
                            rs3.getString( "lig_recebida" ),
                            rs3.getString( "lig_realizada" ),
                            rs3.getString( "ticket" ),
                            rs3.getString( "ref_ticket" ),
                            rs3.getString( "responsavel" ),
                            data,
                            rs3.getString( "hora" )
                        } );
            }


        } catch ( Exception e )
        {
            e.printStackTrace();
        }
    }

    public void personalizaTabela()
    {
        DefaultTableCellRenderer centralizado = new DefaultTableCellRenderer();
        centralizado.setHorizontalAlignment( SwingConstants.CENTER );


        //Coluna ID oculta
        tblLigacoes.getColumnModel().getColumn( 0 ).setMaxWidth( 0 );
        tblLigacoes.getColumnModel().getColumn( 0 ).setMinWidth( 0 );
        tblLigacoes.getTableHeader().getColumnModel().getColumn( 0 ).setMaxWidth( 0 );
        tblLigacoes.getTableHeader().getColumnModel().getColumn( 0 ).setMinWidth( 0 );
        tblLigacoes.getColumnModel().getColumn( 5 ).setMaxWidth( 0 );
        tblLigacoes.getColumnModel().getColumn( 5 ).setMinWidth( 0 );
        tblLigacoes.getTableHeader().getColumnModel().getColumn( 5 ).setMaxWidth( 0 );
        tblLigacoes.getTableHeader().getColumnModel().getColumn( 5 ).setMinWidth( 0 );


        //Tamanho colunas
        tblLigacoes.getColumnModel().getColumn( 1 ).setPreferredWidth( 80 );
        tblLigacoes.getColumnModel().getColumn( 2 ).setPreferredWidth( 40 );
        tblLigacoes.getColumnModel().getColumn( 3 ).setPreferredWidth( 40 );
        tblLigacoes.getColumnModel().getColumn( 4 ).setPreferredWidth( 60 );
        tblLigacoes.getColumnModel().getColumn( 6 ).setPreferredWidth( 60 );
        tblLigacoes.getColumnModel().getColumn( 7 ).setPreferredWidth( 20 );
        tblLigacoes.getColumnModel().getColumn( 8 ).setPreferredWidth( 20 );

        // Instala o TableRowSorter.
        TableRowSorter<TableModel> sorter;
        sorter = new TableRowSorter<TableModel>( modeloTabela );
        tblLigacoes.setRowSorter( sorter );

        //ordena colunas 1,2 tabela
        int colunas[] =
        {
            2, 3
        };
        OrdenaColunaJTable.ordenaTabela( colunas, modeloTabela, tblLigacoes );

        // Centraliza valores tabela
        for ( int x = 2; x < modeloTabela.getColumnCount(); x++ )
        {
            tblLigacoes.getColumnModel().getColumn( x ).setCellRenderer( centralizado );
        }
    }

    public void limpaModelo()
    {
        int numLinhas = modeloTabela.getRowCount();


        for ( int x = numLinhas; x > 0; x-- )
        {
            modeloTabela.removeRow( 0 );
        }


    }

    public void realizaFiltro()
    {

        FormPrincipal.verificaSessao();
        FormPrincipal.verificaTipoUsuario();


        SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" );
        String dtInicio = "";
        String dtFim = "";
        String horaInicio = txt_hora_inicio.getText();
        String horaFim = txt_hora_fim.getText();
        Object usuario = cbxUsuarios.getSelectedItem();
        Object cliente = cbxClientes.getSelectedItem();
        String ticket = tfCodigoTicket.getText();



        try
        {
            dtInicio = sdf.format( dt_inicio.getDate() );
        } catch ( Exception e )
        {
        }


        try
        {

            dtFim = sdf.format( dt_fim.getDate() );
        } catch ( Exception e )
        {
        }


        consulta = "SELECT b.id, "
                + "a.descricao as cliente, "
                + "b.ref_cliente, "
                + "b.lig_recebida, "
                + "b.lig_realizada, "
                + "b.ticket, "
                + "b.ref_ticket, "
                + "c.nome as responsavel, "
                + "b.ref_usuario, "
                + "b.data, "
                + "b.hora "
                + "FROM clientes a "
                + "INNER JOIN ligacoes b "
                + "ON a.id=b.ref_cliente "
                + "INNER JOIN usuarios c "
                + "ON b.ref_usuario=c.id WHERE 1=1 ";

        String totalLigacoes = "SELECT sum(lig_recebida) + sum(lig_realizada) as total, "
                + "sum(lig_recebida)as lig_recebida, "
                + "sum(lig_realizada)as lig_realizada "
                + "FROM clientes a "
                + "INNER JOIN ligacoes b "
                + "ON a.id=b.ref_cliente "
                + "INNER JOIN usuarios c "
                + "ON b.ref_usuario=c.id WHERE 1=1 ";

        String condicao = "";


        Data dt = new Data();


        //responsavel

        if ( usuario.toString().equalsIgnoreCase( "Todos" ) == false )
        {
            condicao += " AND c.nome = '" + usuario.toString() + "' ";
        }

        if ( cliente.toString().equalsIgnoreCase( "Todos" ) == false )
        {
            condicao += "AND a.descricao = '" + cliente.toString() + "' ";
        }

        //data

        if ( ( dt.inverteSql( dtInicio ).equalsIgnoreCase( "0" ) == false ) && ( dt.inverteSql( dtFim ).equalsIgnoreCase( "0" ) == false ) )
        {
            condicao += " AND b.data BETWEEN '" + dt.inverteSql( dtInicio ) + "' AND '" + dt.inverteSql( dtFim ) + "' ";
        } else if ( ( dt.inverteSql( dtInicio ).equalsIgnoreCase( "0" ) == false ) && ( dt.inverteSql( dtFim ).equalsIgnoreCase( "0" ) ) )
        {
            condicao += " AND b.data >= '" + dt.inverteSql( dtInicio ) + "' ";
        } else if ( ( dt.inverteSql( dtInicio ).equalsIgnoreCase( "0" ) ) && ( dt.inverteSql( dtFim ).equalsIgnoreCase( "0" ) == false ) )
        {
            condicao += " AND b.data <= '" + dt.inverteSql( dtFim ) + "' ";
        }

        //hora

        if ( ( dt.validaHora( horaInicio ).equalsIgnoreCase( "0" ) == false ) && ( dt.validaHora( horaFim ).equalsIgnoreCase( "0" ) == false ) )
        {
            condicao += " AND b.hora BETWEEN '" + horaInicio + "' AND '" + horaFim + "' ";
        } else if ( ( dt.validaHora( horaInicio ).equalsIgnoreCase( "0" ) ) && ( dt.validaHora( horaFim ).equalsIgnoreCase( "0" ) == false ) )
        {
            condicao += " AND b.hora >= '" + horaInicio + "' ";
        } else if ( ( dt.validaHora( horaInicio ).equalsIgnoreCase( "0" ) == false ) && ( dt.validaHora( horaFim ).equalsIgnoreCase( "0" ) ) )
        {
            condicao += " AND b.hora <= '" + horaFim + "' ";
        }

        //ticket
        if ( ticket != null && !ticket.equalsIgnoreCase( "" ) )
        {
            if ( new ValidaString( ticket ).getValida() != false )
            {
                condicao += " AND b.ticket like '%" + ticket + "%' ";
            } else
            {
                JOptionPane.showMessageDialog( null, "Caracter inválido no campo ticket" );
                tfCodigoTicket.setText( null );
            }
        }



        //Cria consulta
        consulta += condicao + " ORDER BY b.id ";

        //Cria consulta para Total Ligacoes
        totalLigacoes += condicao;

        //Total ligacoes labels;
        mostraTotal( totalLigacoes );

        limpaModelo();


        //Tabelas ligacoes
        criaModeloTabela( consulta );
        personalizaTabela();

    }

    public void mostraTotal( String consulta )
    {

        Banco conn = new Banco();
        ResultSet rs = conn.executarConsulta( consulta );
        String total = "";
        String total_recebida = "";
        String total_realizada = "";

        try
        {
            while ( rs.next() )
            {
                total = rs.getString( "total" );
                total_recebida = rs.getString( "lig_recebida" );
                total_realizada = rs.getString( "lig_realizada" );
            }
        } catch ( Exception e )
        {
        }
        conn.fecha();

        lblTotal_ligacoes.setText( total );
        lblTotal_recebidas.setText( total_recebida );
        lblTotal_realizadas.setText( total_realizada );

    }

    public void actions()
    {

        btnImprimir.addActionListener( new ActionListener()
        {

            public void actionPerformed( ActionEvent ae )
            {
                String dtInicio = "";
                String dtFim = "";
                SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" );

                limpaModelo();
                realizaFiltro();


               try
                {

                    Banco conn = new Banco();
                    ResultSet rs = conn.executarConsulta( consulta );
                    conn.fecha();

                    JRDataSource jrd = new JRResultSetDataSource( rs );
                    JasperReport j = ( JasperReport ) JRLoader.loadObject( getClass().getResource( "/relatorios/relatorio2.jasper" ) );

                    HashMap a = new HashMap();

                    //busca data inicio
                    try
                    {
                        dtInicio = sdf.format( dt_inicio.getDate() );

                        if( !dtInicio.equalsIgnoreCase( "" ) )
                        {
                            a.put( "dt_inicio", dtInicio );
                        }

                    } catch ( Exception g )
                    {
                    }


                    //busca data fim
                    try
                    {

                        dtFim = sdf.format( dt_fim.getDate() );

                        if( !dtFim.equalsIgnoreCase( "" ) )
                        {
                            a.put( "dt_fim", dtFim );
                        }

                    } catch ( Exception g )
                    {
                    }

                    a.put( "total_ligacoes", lblTotal_ligacoes.getText() );
                    a.put( "lig_recebidas", lblTotal_recebidas.getText() );
                    a.put( "lig_realizadas", lblTotal_realizadas.getText() );


                    JasperPrint p = JasperFillManager.fillReport( j, a, jrd );
                    new JasperViewer( p, false ).setVisible( true );

                }catch( Exception k )
                {
                    k.printStackTrace();
                }

            }
        } );

        btnLimpar.addActionListener( new ActionListener()
        {

            public void actionPerformed( ActionEvent ae )
            {
                cbxClientes.setSelectedIndex( 0 );
                cbxUsuarios.setSelectedIndex( 0 );
                dt_inicio.setDate( null );
                dt_fim.setDate( null );
                txt_hora_inicio.setText( null );
                txt_hora_fim.setText( null );
                tfCodigoTicket.setText( null );
                limpaModelo();
                realizaFiltro();
            }
        } );

        tblLigacoes.addMouseListener( new MouseListener()
        {

            @Override
            public void mouseClicked( MouseEvent e )
            {
                if ( e.getButton() == MouseEvent.BUTTON3 && tblLigacoes.getSelectedRow() != -1 )
                {
                    int ticket = Integer.parseInt( tblLigacoes.getValueAt( tblLigacoes.getSelectedRow(), 5 ).toString() );
                    String responsavel = tblLigacoes.getValueAt( tblLigacoes.getSelectedRow(), 6 ).toString();

                    if ( ticket != 0 )
                    {

                        pop.show( tblLigacoes, e.getX(), e.getY() );

                    } else
                    {
                        int lig_recebida = Integer.parseInt( tblLigacoes.getValueAt( tblLigacoes.getSelectedRow(), 2 ).toString() );

                        if ( lig_recebida != 0 )
                        {

                            if ( responsavel.equalsIgnoreCase( FormPrincipal.getNome_usuario() ) )
                            {
                                pop2.show( tblLigacoes, e.getX(), e.getY() );
                            } else
                            {
                                JOptionPane.showMessageDialog( null, "Não é possível cadastrar ticket para ligações \n"
                                        + "de responsabilidade de outro usuário" );
                            }

                        } else
                        {
                            JOptionPane.showMessageDialog( null, "Não é possível cadastrar ticket para ligações realizadas" );
                        }
                    }

                }

                if ( e.getButton() == MouseEvent.BUTTON3 && tblLigacoes.getSelectedRow() == -1 )
                {
                    JOptionPane.showMessageDialog( null, "Selecione uma linha da tabela de ligações" );
                }
            }

            @Override
            public void mousePressed( MouseEvent e )
            {
            }

            @Override
            public void mouseReleased( MouseEvent e )
            {
            }

            @Override
            public void mouseEntered( MouseEvent e )
            {
            }

            @Override
            public void mouseExited( MouseEvent e )
            {
            }
        } );

        itemVisualizar.addActionListener( new ActionListener()
        {

            @Override
            public void actionPerformed( ActionEvent e )
            {
                inspectOccurrence( Integer.parseInt( tblLigacoes.getValueAt( tblLigacoes.getSelectedRow(), 5 ).toString() ) );
            }
        } );

        itemCadastrarTicket.addActionListener( new ActionListener()
        {

            @Override
            public void actionPerformed( ActionEvent e )
            {
                int id_ligacao = Integer.parseInt( tblLigacoes.getValueAt( tblLigacoes.getSelectedRow(), 0 ).toString() );

                createOccurrence( id_ligacao );

                realizaFiltro();
            }
        } );

        dt_inicio.addActionListener( new ActionListener()
        {

            @Override
            public void actionPerformed( ActionEvent e )
            {
                limpaModelo();
                realizaFiltro();
            }
        } );

        dt_fim.addActionListener( new ActionListener()
        {

            @Override
            public void actionPerformed( ActionEvent e )
            {
                limpaModelo();
                realizaFiltro();
            }
        } );

        cbxClientes.addActionListener( new ActionListener() {

            @Override
            public void actionPerformed( ActionEvent e )
            {
                limpaModelo();
                realizaFiltro();
            }
        });

        cbxUsuarios.addActionListener( new ActionListener() {

            @Override
            public void actionPerformed( ActionEvent e )
            {
                limpaModelo();
                realizaFiltro();
            }
        });

        txt_hora_inicio.addActionListener( new ActionListener() {

            @Override
            public void actionPerformed( ActionEvent e )
            {
                limpaModelo();
                realizaFiltro();
            }
        });

        txt_hora_fim.addActionListener( new ActionListener() {

            @Override
            public void actionPerformed( ActionEvent e )
            {
                limpaModelo();
                realizaFiltro();
            }
        });

        tfCodigoTicket.addActionListener( new ActionListener() {

            @Override
            public void actionPerformed( ActionEvent e )
            {
                limpaModelo();
                realizaFiltro();
            }
        });
    }


    private void inspectOccurrence( int id_ticket )
    {
        try
        {
            Login l = com.interact.sas.cmn.ModuleContext.getInstance().getLoginManager().userLogin( FormPrincipal.getLogin_usuario() );

            if ( l != null )
            {

                ApplicationContext.getInstance().setActiveLogin( l );

                Occurrence o = com.interact.sas.qms.ModuleContext.getInstance().getOccurrenceManager().getOccurrence( id_ticket );

                Inspector.inspect( this, o );

            } else
            {
                Prompts.alert( this, "Usuario inválido" );
            }

        } catch ( Exception e )
        {
            e.printStackTrace();
        }
    }

    private void createOccurrence( int id_ligacao )
    {
        try
        {
            Login l = com.interact.sas.cmn.ModuleContext.getInstance().getLoginManager().userLogin( FormPrincipal.getLogin_usuario() );

            if ( l != null )
            {
                ApplicationContext.getInstance().setActiveLogin( l );

                OccurrenceType ot = com.interact.sas.qms.ModuleContext.getInstance().getOccurrenceTypeManager().getOccurrenceType( properties.getTipoOcorrencia() );



                if ( OccurrenceFormFactory.getInstance().edit( this, ot.getMnemonic() ) )
                {
                    Occurrence o = ( ( Occurrence ) OccurrenceFormFactory.getInstance().getForm( ot.getMnemonic() ).getContext() );

                    getSerial();

                    try
                    {
                        Banco conn2 = new Banco();
                        conn2.executarAtualizacao( "UPDATE ligacoes set ticket = '" + ocorrencia.getSerial() + "', ref_ticket=" + ocorrencia.getId() + " WHERE id= " + id_ligacao );
                        conn2.fecha();

                        realizaFiltro();

                    } catch ( Exception e )
                    {
                        JOptionPane.showMessageDialog( null, "Erro de banco de dados : " + e, "Erro", JOptionPane.ERROR );


                    }

                }
            } else
            {
                Prompts.alert( this, "Usuario inválido" );
            }

        } catch ( Exception e )
        {
            e.printStackTrace();
        }
    }

    private void getSerial()
    {
        try
        {

            QueryResult rs = Database.getInstance().executeQuery( "SELECT max(id), max(serial) FROM qms_occurrences WHERE ref_type = " + properties.getTipoOcorrencia() );

            if ( rs.next() )
            {
                this.ocorrencia.setId( rs.getInt( 1 ) );
                this.ocorrencia.setSerial( rs.getString( 2 ) );
            }


        } catch ( Exception e )
        {
            e.printStackTrace();
        }

    }
}
